//express_demo.js 文件
var express = require('express');
var app = express();
moment = require('moment')

var bodyParser = require('body-parser');
//解释参考http://blog.csdn.net/yanyang1116/article/details/54847560

// 创建 application/json 解析
var jsonParser = bodyParser.json();
// 创建 application/x-www-form-urlencoded 编码解析
var urlencodedParser = bodyParser.urlencoded({ extended: false });

// 创建数据库连接
const mysql = require('mysql')
const conn = mysql.createConnection({
    //host:'39.96.71.104',
    host:'localhost',
    user:'root',
    password:'arcgis',
    database:'nodedemo'
})

// 根据ID 获取相关数据
app.get('/api/getusers',(req,res) => {

    conn.connect();
    console.log("数据库连接成功 success")
    
    const sqlStr = 'select * from users ' 
    conn.query(sqlStr,(err,results) => {
        if(err) return res.json({code:1,data:'获取数据失败',message:"失败"})
        res.json({
            code:0,
            data:results,
            message:"成功"
        })
    })
})
app.get('/api/adduser',(req,res)=>{

    conn.connect();
    var addSql = "INSERT INTO users(UserId,UserName,RoleCode,PassWord,Phone,Position,Remark,Village,RiverRole,District,RiverLake,River,Time) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)";
    console.log(moment(new Date()).format('YYYY-MM-DD HH:mm:ss'));
    var addSqlParams = [111111,'宋向阳',1,'123456','17792573252','','','','','','','',moment(new Date()).format('YYYY-MM-DD HH:mm:ss')];
    conn.query(addSql,addSqlParams,function (err, result) {
        if(err) {
            res.json({code:1,data:'插入数据失败',message:err})
            console.log("插入数据失败:"+err);
            return;
        }else
        {
            res.json({code:0,data:result.affectedRows,message:"成功"}); 
            console.log("插入数据成功");
        }
    });
})

app.post('/api/adduserpost',jsonParser,(req,res)=>{
    conn.connect();
      
    // {
    //     "UserId": 100192,
    //     "UserName": "石文军",
    //     "RoleCode": 2,
    //     "PassWord": "xfcqz2HNCeY=",
    //     "Phone": "13947141291",
    //     "Position": "水利工程管理站站长",
    //     "Remark": null,
    //     "Village": null,
    //     "RiverRole": "县级河长",
    //     "District": "和林格尔县",
    //     "RiverLake": null,
    //     "River": "所有河流",
    //     "Time": "2019-09-08T05:35:04.000Z"
    //     }

    var addSql = "INSERT INTO users(UserId,UserName,RoleCode,PassWord,Phone,Position,Remark,Village,RiverRole,District,RiverLake,River,Time) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)";
    //console.log(req.body);
    //console.log(req.body.UserId+req.body.UserName,req.body.Time);
    
    var addSqlParams=[req.body.UserId,req.body.UserName,req.body.RoleCode,req.body.PassWord,req.body.Phone,req.body.Position,req.body.Remark,req.body.Village,req.body.RiverRole,req.body.District,req.body.RiverLake,req.body.River,moment(req.body.Time).format('YYYY-MM-DD HH:mm:ss')];
    conn.query(addSql,addSqlParams,function (err, result) {
        if(err) {
            res.json({code:1,data:'插入数据失败',message:err})
            console.log("插入数据失败:"+err);
            //return;
        }else
        {
            res.json({code:0,data:addSqlParams,message:"成功"}); 
            console.log("插入数据成功");
        }
    });
})
app.post('/api/updateuser',jsonParser,(req,res)=>{
    conn.connect();
    var modSql = 'UPDATE users SET UserName = ?,Phone = ? WHERE UserId = ?';
    var modSqlParams=[req.body.UserName,req.body.Phone,req.body.UserId];
    conn.query(modSql,modSqlParams,function (err, result) {
        if(err) {
            res.json({code:1,data:'更新数据失败',message:err})
            console.log("更新数据失败:"+err);
            return;
        }else
        {
            res.json({code:0,data:modSqlParams,message:"成功"}); 
            console.log("更新数据成功");
        }
    });
})

app.delete('/api/deleteuser',(req,res)=>{
    conn.connect();
    
    var delSql = 'DELETE FROM users where UserId =?';
    console.log(req.query);
    var delParams=req.query.UserId;
    conn.query(delSql,delParams,function (err, result) {
        if(err) {
            res.json({code:1,data:'删除数据失败',message:err})
            console.log("删除数据失败:"+err);
            return;
        }else
        {
            res.json({code:0,data:delParams,message:"成功"}); 
            console.log("删除数据成功");
        }
    });
})

var server = app.listen(8081, function () {
 
  var host = server.address().address
  var port = server.address().port
 
  console.log("应用实例，访问地址为 http://%s:%s", host, port)
 
})
